package com.gk.javaservice.util.auto;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
//自动更新数据库结构
@Component
public class DatabaseInitRunner implements CommandLineRunner {

    private static final Logger log = LoggerFactory.getLogger(DatabaseInitRunner.class);


    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
//        addTalkColumnIfNotExists();
//        createfilestalkTableIfNotExists();
        createPersonnelTableIfNotExists();
    }
    //2025-08-20
    private void addTalkColumnIfNotExists() {
        String tableName = "files";
        String columnName = "talk";
        String databaseName = "meetingdb";

        // 检查字段是否存在：COUNT(*) 返回 0 或 1
        String checkSql = "SELECT COUNT(*) " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "AND COLUMN_NAME = ?";


        try {
            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, databaseName, tableName, columnName);

            if (count != null && count == 0) {
                // 字段不存在，添加字段
                String addColumnSql = "ALTER TABLE " + tableName +
                        " ADD COLUMN " + columnName +
                        " INT DEFAULT 0 COMMENT 'talk字段'";

                jdbcTemplate.execute(addColumnSql);
                System.out.println("✅ 已成功添加字段: " + columnName + " 到表 " + tableName);
            } else {
                System.out.println("🔍 字段 " + columnName + " 已存在，无需添加。");
            }
        } catch (Exception e) {
            System.err.println("❌ 检查数据库字段时出错：" + e.getMessage());
            // 不中断启动
        }
    }
    //加表示例
    private void createPatientCaseTableIfNotExists() {
        String tableName = "patient_case";
        String databaseName = "meetingdb"; // 请根据实际数据库名修改

        try {
            // 检查表是否存在
            String checkSql = "SELECT COUNT(*) " +
                    "FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_SCHEMA = ? " +
                    "AND TABLE_NAME = ?";

            Integer count = 0;
            try {
                count = jdbcTemplate.queryForObject(checkSql, Integer.class, databaseName, tableName);
            } catch (Exception e) {
                log.warn("⚠️ 查询表 [{}] 是否存在时发生异常: {}", tableName, e.getMessage());
                return; // 如果查询失败，暂时跳过（可重试机制可后续加）
            }

            if (count != null && count == 0) {
                // 表不存在，执行建表语句
                String createTableSql =
                        "CREATE TABLE patient_case ( " +
                                "  id int(11) NOT NULL AUTO_INCREMENT, " +
                                "  name longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  sex longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  surgery_date datetime NULL DEFAULT NULL, " +
                                "  hospital_number longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  bed_number longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  department longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  operating_room longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  surgery_name longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  age int(11) NULL DEFAULT NULL, " +
                                "  ward longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  surgery_doctor longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL, " +
                                "  created_at datetime NULL DEFAULT NULL, " +
                                "  updated_at datetime NULL DEFAULT NULL, " +
                                "  dep_id int(11) NULL DEFAULT NULL, " +
                                "  user_id int(11) NULL DEFAULT NULL, " +
                                "  for_purid int(11) NULL DEFAULT 1, " +
                                "  is_link tinyint(1) NULL DEFAULT 0, " +
                                "  patientLid varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  pacs_url varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  status varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  preface int(11) NULL DEFAULT NULL, " +
                                "  anesthesiologist varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  nurse varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  operationID varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " +
                                "  PRIMARY KEY (id) USING BTREE " +
                                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT";

                try {
                    jdbcTemplate.execute(createTableSql);
                    log.info("✅ 成功创建表: {}", tableName);
                } catch (Exception e) {
                    log.error("❌ 创建表 [{}] 失败！SQL 执行异常: {}", tableName, e.getMessage());
                    log.debug("完整异常:", e); // 仅在 debug 模式下输出堆栈
                }

            } else {
                log.info("🔍 表 [{}] 已存在，跳过创建。", tableName);
            }

        } catch (Exception e) {
            log.error("❌ 检查表 [{}] 是否存在时发生未知错误：{}", tableName, e.getMessage());
            log.debug("完整异常:", e);
        }
    }

    private void createfilestalkTableIfNotExists() {
        String tableName = "filestalk";
        String databaseName = "meetingdb"; // 请根据实际数据库名修改

        try {
            // 检查表是否存在
            String checkSql = "SELECT COUNT(*) " +
                    "FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_SCHEMA = ? " +
                    "AND TABLE_NAME = ?";

            Integer count = 0;
            try {
                count = jdbcTemplate.queryForObject(checkSql, Integer.class, databaseName, tableName);
            } catch (Exception e) {
                log.warn("⚠️ 查询表 [{}] 是否存在时发生异常: {}", tableName, e.getMessage());
                return;
            }

            if (count != null && count == 0) {
                // 表不存在，执行建表语句
                String createTableSql =
                        "CREATE TABLE filestalk ( " +
                                "  id int NOT NULL AUTO_INCREMENT, " +
                                "  meeting_id int NULL DEFAULT NULL, " +
                                "  type int NULL DEFAULT NULL, " +
                                "  name longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL, " +
                                "  path longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL, " +
                                "  status int NULL DEFAULT NULL, " +
                                "  created_at datetime NULL DEFAULT NULL, " +
                                "  updated_at datetime NULL DEFAULT NULL, " +
                                "  for_purid int NULL DEFAULT 1, " +
                                "  for_depid int NULL DEFAULT NULL, " +
                                "  for_userid int NULL DEFAULT NULL, " +
                                "  full_path longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL, " +
                                "  createtime timestamp NULL DEFAULT CURRENT_TIMESTAMP, " +
                                "  size varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, " +
                                "  url varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, " +
                                "  file_name varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, " +
                                "  PRIMARY KEY (id) USING BTREE " +
                                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT";

                try {
                    jdbcTemplate.execute(createTableSql);
                    log.info("✅ 成功创建表: {}", tableName);
                } catch (Exception e) {
                    log.error("❌ 创建表 [{}] 失败！SQL 执行异常: {}", tableName, e.getMessage());
                    log.debug("完整异常:", e);
                }
            } else {
                log.info("🔍 表 [{}] 已存在，跳过创建。", tableName);
            }

        } catch (Exception e) {
            log.error("❌ 检查表 [{}] 是否存在时发生未知错误：{}", tableName, e.getMessage());
            log.debug("完整异常:", e);
        }
    }

    private void createPersonnelTableIfNotExists() {
        String tableName = "ats_personnel";
        String databaseName = "assets-tracking-system";

        try {
            // 检查表是否存在
            String checkSql = "SELECT COUNT(*) " +
                    "FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

            Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, databaseName, tableName);

            if (count != null && count == 0) {
                String createTableSql =
                        "CREATE TABLE `ats_personnel` ( " +
                                "  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键', " +
                                "  `user_id` VARCHAR(100) NOT NULL COMMENT '用户唯一标识符（GUID）', " +
                                "  `create_time` DATETIME(6) NULL DEFAULT NULL COMMENT '用户创建时间', " +
                                "  `user_key` VARCHAR(100) NULL DEFAULT NULL COMMENT '用户关键字/登录名', " +
                                "  `name` VARCHAR(100) NULL DEFAULT NULL COMMENT '用户姓名或显示名称', " +
                                "  `picture` TEXT NULL DEFAULT NULL COMMENT '用户头像URL或Base64', " +
                                "  `division_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属事业部ID', " +
                                "  `division` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属事业部名称', " +
                                "  `division_code` VARCHAR(50) NULL DEFAULT NULL COMMENT '所属事业部代码', " +
                                "  `department_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属部门ID', " +
                                "  `department` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属部门名称', " +
                                "  `department_code` VARCHAR(50) NULL DEFAULT NULL COMMENT '所属部门代码', " +
                                "  `section_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属课别/小组ID', " +
                                "  `section` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属课别/小组名称', " +
                                "  `section_code` VARCHAR(50) NULL DEFAULT NULL COMMENT '所属课别/小组代码', " +
                                "  `group_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属组ID', " +
                                "  `group_name` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属组名称', " +
                                "  `group_code` VARCHAR(50) NULL DEFAULT NULL COMMENT '所属组代码', " +
                                "  `title` VARCHAR(100) NULL DEFAULT NULL COMMENT '职位/职称', " +
                                "  `gender` TINYINT NULL DEFAULT 0 COMMENT '性别（0:未知,1:男,2:女）', " +
                                "  `mobile` VARCHAR(20) NULL DEFAULT NULL COMMENT '手机号码', " +
                                "  `office_tel` VARCHAR(20) NULL DEFAULT NULL COMMENT '办公电话', " +
                                "  `emergency_contact` VARCHAR(100) NULL DEFAULT NULL COMMENT '紧急联系人', " +
                                "  `emergency_contact_phone` VARCHAR(20) NULL DEFAULT NULL COMMENT '紧急联系人电话', " +
                                "  `user_type` INT NULL DEFAULT NULL COMMENT '用户类型', " +
                                "  `account` VARCHAR(100) NULL DEFAULT NULL COMMENT '账号', " +
                                "  `mail` VARCHAR(255) NULL DEFAULT NULL COMMENT '邮箱地址', " +
                                "  `modify_time` DATETIME(6) NULL DEFAULT NULL COMMENT '最后修改时间', " +
                                "  `memo` TEXT NULL DEFAULT NULL COMMENT '备注信息', " +
                                "  `label` VARCHAR(255) NULL DEFAULT NULL COMMENT '标签', " +
                                "  `link_map_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '关联地图ID', " +
                                "  `is_delete` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已删除（0:否,1:是）', " +
                                "  `belong_map_id` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属地图ID', " +
                                "  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间', " +
                                "  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间', " +
                                "  PRIMARY KEY (`id`), " +
                                "  UNIQUE KEY `uk_user_id` (`user_id`), " +
                                "  INDEX `idx_section_id` (`section_id`), " +
                                "  INDEX `idx_belong_map_id` (`belong_map_id`), " +
                                "  INDEX `idx_user_key` (`user_key`), " +
                                "  INDEX `idx_mobile` (`mobile`) " +
                                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='人员信息表（可追踪资产）'";

                jdbcTemplate.execute(createTableSql);
                log.info("✅ 成功创建表: {}", tableName);
            } else {
                log.info("🔍 表 [{}] 已存在，跳过创建。", tableName);
            }
        } catch (Exception e) {
            log.error("❌ 创建表 [{}] 失败: {}", tableName, e.getMessage());
            log.debug("完整异常:", e);
        }
    }
}
